/*
 * Project:     My Project Work, Copyright (C) 2011
 * Created at:  05.08.2011
 */
package de.myprojectwork.model;

import de.myprojectwork.common.util.Logger;
import de.myprojectwork.common.util.MyProjectWorkException;
import de.myprojectwork.common.util.TodoOrderNumberSorter;
import de.myprojectwork.model.dto.AbstractDto;
import de.myprojectwork.model.dto.Project;
import de.myprojectwork.model.dto.Todo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

/**
 *
 * @author Chris Holdschuh
 */
public class DataManipulationModelImpl extends AbstractModel implements DataManipulationModel {
    
    private final String TABLE_PLACEHOLDER = "<table>";
    
    private final String SELECT_ALL = 
            "SELECT * FROM " + TABLE_PLACEHOLDER + " T ";
    
    private final String SELECT_BY_PROJECT = 
            "SELECT * FROM " + TABLE_PLACEHOLDER + " T "
            + "WHERE T.FKPROJECT = ? ";
    
    public DataManipulationModelImpl() {
    }

    @Override
    public <T extends AbstractDto> List<T> get(Table table, Class<T> clazz, Project project) throws MyProjectWorkException {
        List<T> result = new ArrayList<>();
        Connection conn = null;
        try {
            conn = getConnection();
            switch (table) {
                case CONFIGURATION:
                    result = getFromDb(table, clazz, project);
                    break;
                case TODO:
                    result = getFromDb(table, clazz, project);
                    Map<Integer, Integer> childParentIdMap = new HashMap<>(); // key=child, value=parent
                    Map<Integer, Todo> idMap = new HashMap<>(); // zur Verbesserung der Performence
                    for (T t : result) {
                        Todo todo = (Todo) t;
                        idMap.put(todo.getId(), todo);
                        childParentIdMap.put(todo.getId(), todo.getFkParent());
                        if (project != null) {
                            todo.setProject(project);
                        } else {
                            Logger.warn("Kein Projekt für TODO gesetzt.");
                        }
                    }
                    // Zuordnung Parent-Child
                    for (T t : result) {
                        Todo child = (Todo) t;
                        if (childParentIdMap.containsKey(child.getId())) {
                            child.setParent(idMap.get(childParentIdMap.get(child.getId())));
                        }
                    }
                    break;
                case PROJECT:
                    result = getFromDb(table, clazz, project);
                    break;
                default:
                    throw new MyProjectWorkException("Das Auslesen von Daten aus der Tabelle " + table.tableName() 
                            + " ist nicht implementiert.");
            }
            
        } catch (SQLException ex) {
            Logger.error("Fehler beim laden der Daten aus der Tabelle " + table, ex);
            throw new MyProjectWorkException("Fehler beim laden der Daten aus der Tabelle " + table, ex);
            
        } finally {
            close(conn);
        }
        return result;
    }

    @Override
    public <T extends AbstractDto> void save(List<T> list) throws MyProjectWorkException {
        if (list != null && !list.isEmpty()) {
            // Tabelle herausfinden
            Table table = null;
            for (Table t : Table.values()) {
                if (list.get(0) != null && list.get(0).getClass().equals(t.clazz())) {
                    table = t;
                }
            }
            // Daten speichern
            int i = 1;
            QueryRunner run = new QueryRunner();
            Connection conn = null;
            try {
                conn = getConnection();
                switch (table) {
                    case PROJECT:
                        for (T t : list) {
                            Project e = (Project) t;
                            if (e.getId() > 0) {
                                run.update(conn, "UPDATE " + table + " SET "
                                    + "number=?,name=?,customer=?,datestart=?,dateend=?,statusname=?,statuspercent=?,update=SYSDATE "
                                    + "WHERE id=?",
                                    e.getNumber(),e.getName(),e.getCustomer(),e.getDateStart(),e.getDateEnd(),
                                    e.getStatusName(),e.getStatusPercent(),e.getId());
                                
                            } else {
                                e.setId(getNextId());
                                run.update(conn, "INSERT INTO " + table 
                                    + " (id,number,name,customer,datestart,dateend,statusname,statuspercent,update) "
                                    + "VALUES (?,?,?,?,?,?,?,?,SYSDATE)",
                                    e.getId(),e.getNumber(),e.getName(),e.getCustomer(),e.getDateStart(),e.getDateEnd(),
                                    e.getStatusName(),e.getStatusPercent());
                            }
                            t.setId(e.getId());
                        }
                        break;
                    case TODO:
                        for (T t : list) {
                            Todo e = (Todo) t;
                            // korrigiere Parent-Beziehung auf sich selbst
                            if (e.getFkParent() == e.getId()) {
                                e.setParent(null);
                                Logger.warn("Bei Speichern eines Todos wurde eine Parent-Beziehung auf sich selbst gefunden. "
                                        + "Diese Beziehung ist nicht erlaubt und wurde entfernt.");
                            }
                            // speichere Parent
                            if (e.getParent() != null) {
                                List<Todo> parent = new ArrayList<>();
                                parent.add(e.getParent());
                                save(parent);
                            }
                            // speichere oder aktualisiere Todo
                            if (e.getId() > 0) {
                                run.update(conn, "UPDATE " + table + " SET "
                                    + "fkproject=?,fkparent=?,ordernumber=?,targetdate=?,statuspercent=?,text=?,update=SYSDATE "
                                    + "WHERE id=?",
                                    e.getFkProject(),getNumber(e.getFkParent()),e.getOrderNumber(),e.getTargetDate(),
                                    e.getStatusPercent(),e.getText(),e.getId());
                                
                            } else {
                                e.setId(getNextId());
                                run.update(conn, "INSERT INTO " + table 
                                    + " (id,fkproject,fkparent,ordernumber,targetdate,statuspercent,text,update) "
                                    + "VALUES (?,?,?,?,?,?,?,SYSDATE)",
                                    e.getId(),e.getFkProject(),getNumber(e.getFkParent()),e.getOrderNumber(),e.getTargetDate(),
                                    e.getStatusPercent(),e.getText());
                            }
                            t.setId(e.getId());
                        }
                        break;
                    default:
                        throw new MyProjectWorkException("Das Speichern von Daten in die Tabelle " + table 
                                + " ist noch nicht implementiert");
                }
            } catch (SQLException ex) {
                throw new MyProjectWorkException("Fehler beim Speichern der Daten in die Tabelle " + table, ex);
            } finally {
                close(conn);
            }
        }
    }

    @Override
    public <T extends AbstractDto> void save(T entry) throws MyProjectWorkException {
        List<T> list = new ArrayList<>();
        list.add(entry);
        save(list);
    }
    
    @Override
    public <T extends AbstractDto> void delete(T entry) throws MyProjectWorkException {
        if (entry == null || entry.getId() == 0) {
            return;
        }
        
        // Tabelle herausfinden
        Table table = null;
        for (Table t : Table.values()) {
            if (entry.getClass().equals(t.clazz())) {
                table = t;
            }
        }
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("DELETE FROM " + table + " WHERE ID = " + entry.getId());
            ps.execute();
            
        } catch (SQLException ex) {
            throw new MyProjectWorkException("Fehler beim Laden der Daten aus der Tabelle " + table, ex);
            
        } finally {
            close(conn);
            close(ps);
        }
    }

    @Override
    public void reorgTodoOrderNumbers(Project project) throws MyProjectWorkException {
        List<Todo> todoList = get(Table.TODO, Table.TODO.clazz(), project);
        reorgOrderNumbers(todoList, null);
        save(todoList);
    }
    
    private <T extends AbstractDto> List<T> getFromDb(Table table, Class<T> clazz, Project project) throws MyProjectWorkException {
        QueryRunner run = new QueryRunner();
        Connection conn = null;
        try {
            conn = getConnection();
            ResultSetHandler<List<T>> h = new BeanListHandler<>(clazz);
            List<T> result = new ArrayList<>();
            if (project != null) {
                result = run.query(conn, SELECT_BY_PROJECT.replace(TABLE_PLACEHOLDER, table.tableName()), h, project.getId());
            } else {
                result = run.query(conn, SELECT_ALL.replace(TABLE_PLACEHOLDER, table.tableName()), h);
            }
            return result;
            
        } catch (SQLException ex) {
            throw new MyProjectWorkException("Fehler beim Laden der Daten aus der Tabelle " + table, ex);
            
        } finally {
            close(conn);
        }
    }
    
    private Project getProject(int id) throws MyProjectWorkException {
        Project result = null;
        List<Project> list = get(Table.PROJECT, Table.PROJECT.clazz(), null);
        for (Project p : list) {
            if (p.getId() == id)
                result = p;
        }
        return result;
    }
    
    private Integer getNumber(int i) {
        if (i == 0) {
            return null;
        } else {
            return new Integer(i);
        }
    }
    
    private void reorgOrderNumbers(List<Todo> todoList, Todo parent) {
        int parentId = parent != null ? parent.getId() : 0;
        List<Todo> childList = new ArrayList<>();
        for (Todo t : todoList) {
            if (t.getFkParent() == parentId) {
                reorgOrderNumbers(todoList, t); // Rekursion
                childList.add(t);
            }
        }
        Collections.sort(childList, new TodoOrderNumberSorter());
        for (int i = 1; i <= childList.size(); i++) {
            childList.get(i-1).setOrderNumber(i);
        }
    }
}
